-- @owner: lihongji
-- @date: 2022/07/28
-- @testpoint: 存储过程中使用

--step1:建序列;expect:成功
drop sequence if exists sq_ustore_regr_intercept_0027;
create sequence sq_ustore_regr_intercept_0027 increment by 1 start with 10;

--step2:建表并插入数据;expect:成功
drop table if exists t_ustore_regr_intercept_0027 cascade;
create table t_ustore_regr_intercept_0027(
 col_1 integer,
 col_2 number(19,0),
 col_3 number(10,6)
) with (storage_type=ustore);

begin
 for i in 1..50 loop
  insert into t_ustore_regr_intercept_0027 values(
  i,
  sq_ustore_regr_intercept_0027.nextval,
  i+445.255
  );
 end loop;
end;
/

--step3:创建存储过程;expect:成功
drop procedure if exists p_ustore_regr_intercept_0027;
create or replace procedure p_ustore_regr_intercept_0027(num int)
is
 v_lang clob := 'abc';
 sqlstat varchar(10000);
begin
 for i in 1 .. num
 loop
  sqlstat := 'select regr_intercept(col_2,col_2) from t_ustore_regr_intercept_0027 order by 1';
  execute immediate sqlstat;
 end loop;
end;
/

--step4:调用存储过程;expect:成功
call p_ustore_regr_intercept_0027(1);

--step5:清理环境;expect:成功
drop procedure if exists p_ustore_regr_intercept_0027;
drop sequence if exists sq_ustore_regr_intercept_0027;
drop table if exists t_ustore_regr_intercept_0027 cascade;